package board;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;

public class Board {
	
	int fileSize = 10*1024*1024;
	private String uploadPath = "C:\\workspace\\svn_web1403\\WebContent\\upload";
	
	String fileEncoding = "UTF-8";
	
	
	//------------------------------------------------
	//         JDBC 연결
	//------------------------------------------------
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE"; 
	String db_user = "hr";
	String db_pwd = "hr";
	String sql="";

	Connection conn;
			
	BoardVo vo;
	
	//페이지 분리 관련 필드
	private int listSize=3;//한페이지에 보여질 행수

	private int blockSize=2;//보여질 페이지 이동 번호 수.
	
	private int totSize=0;//검색된 데이터의 총 갯수
	private int totPage=0;//전체페이지수
	private int totBlock=0;//전체 블럭수
	
	private int nowPage=1;//현재 페이지
	private int nowBlock=1;//현재 블럭
	
	private int startNo=1;//목록의 시작 위치
	private int endNo=3;//목록의 끝 위치
	private int startPage=1; //현재 블럭의 시작 페이지번호
	private int endPage=2;//현재 블럭의 끝 페이지 번호
	
	
	
	
	
	public Board(){
		try {
			// 1) 드라이버 로딩
			Class.forName(driver).newInstance();
			
			// 2) Connection 객체 생성
			conn = DriverManager.getConnection(url, db_user, db_pwd);
			
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	
	// *****************************************************************
	//              글 삽입하는 역할
	// *****************************************************************
	

	public boolean insert() {
		boolean flag = false;
		try {
			MultipartRequest mr = new MultipartRequest(vo.getRequest(), uploadPath, fileSize, fileEncoding,	new DefaultFileRenamePolicy());
			
			String part = mr.getParameter("part");
			String mid = mr.getParameter("mid");
			String subject = mr.getParameter("subject");
			String content = mr.getParameter("content");
			String pwd = mr.getParameter("pwd");

			String orifile = mr.getOriginalFileName("filename");
			String sysfile = mr.getFilesystemName("filename");
			
			// db저장
			String sql = "insert all"
					+ " into board(serial, part, mid, mdate, subject, pwd, hit, content, grp, deep)"
					+ " values(brd_serial1.nextval, ?, ?, sysdate, ?, ?, 0, ?,brd_serial1.currval, brd_serial1.currval)";
			if (sysfile != null) {
				sql += " into brdfiles(serial, fileid, filename) values(brd_serial2.nextval, brd_serial1.currval,?)";
			}
			sql += " select * from dual";// 이 셀렉이 문장이있어야 파일이 들어간다.  그리고 쿼리문은 끝까지 확인해줘야 한다.

			try {
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1, part);
				ps.setString(2, mid);
				ps.setString(3, subject);
				ps.setString(4, pwd);
				ps.setString(5, content);
	
				if (sysfile != null) {
					ps.setString(6, sysfile);
				}

				int cnt = ps.executeUpdate();
				if (cnt > 0) {
					flag = true;
				}
				ps.close();
				conn.close();
				
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}

		return flag;
	}

	// *****************************************************************
	//            글 받아오기
	// *****************************************************************

	public BoardVo view(){
		BoardVo v = null;
		String sql = null;
		PreparedStatement ps = null;
		 
		try{
			sql = "update board set hit = (hit + 1) where serial=" + vo.getSerial();
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();
			
			sql = "select b.*, f.* from board b left outer join brdfiles f on b.serial = f.fileid where b.serial=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());
			
			ResultSet rs = ps.executeQuery();
			
			if(rs.next()){
				v = new BoardVo();
				
				v.setSerial(rs.getInt("serial"));
				v.setMid(rs.getString("mid"));
				v.setPwd(rs.getString("pwd"));
				v.setSubject(rs.getString("subject"));
				v.setContent(rs.getString("content"));
				v.setMdate(rs.getDate("mdate"));
				v.setFilename(rs.getString("filename"));
				v.setGrp(rs.getInt("grp"));
				v.setDeep(rs.getString("deep"));
				v.setHit(rs.getInt("hit"));
				
			}
			rs.close();
			ps.close();
			conn.close();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return v;
	}
	
	// *****************************************************************
	//            글 삭제 확인
	// *****************************************************************
	public boolean delete(){
		
		boolean flag=false;
		
		sql="delete from board where serial=?";
		
		try{
			
			PreparedStatement ps = conn.prepareStatement(sql);
			
			ps.setInt(1, vo.getSerial());
			
			int cnt = ps.executeUpdate();
			if(cnt>0){
				flag=true;
				File file= new File(uploadPath+"/"+vo.getFilename());
				if(file.exists()){
					file.delete();	
				}else{	
					//고민상담...
				}
				
			}
			
			
		}catch(Exception ex){
			
			ex.printStackTrace();
		}
		
		
		
		return flag;
		
	}

	// *****************************************************************
	//             글 수정 확인
	// *****************************************************************
	public boolean modify(){
		boolean flag = false;
		try{
			MultipartRequest mr= new MultipartRequest(vo.getRequest(),uploadPath,fileSize,fileEncoding,new DefaultFileRenamePolicy());
			String subject= mr.getParameter("subject");
			String content= mr.getParameter("content");
			String pwd= mr.getParameter("pwd");
			String filename = mr.getParameter("filename");
			String sysfile= mr.getParameter("newfilename");
			int serial = Integer.parseInt(mr.getParameter("serial"));
			String fileDelFlag= mr.getParameter("fileDelFlag");
			
			PreparedStatement ps= null;
			int cnt=0;//처리결과
			
			//기본 첨부된 파일 삭제
			if(fileDelFlag.equals("del")){
				fileDel(filename,serial);
			}
			
			//자동commit금지
			conn.setAutoCommit(false);
			
			//새로운 첨부파일이 존재하지 않으면(내용만수정)
			sql="update board set subject=?, content=? where pwd=? and serial=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, subject);
			ps.setString(2, content);
			ps.setString(3, pwd);
			ps.setInt(4, serial);
			cnt=ps.executeUpdate();
				
			if(cnt>0) flag=true;
			
			//새로운 첨부파일이 생성된 경우(내용+첨부파일이 모두 수정된 경우)
			if(sysfile != null || sysfile !=""){		
				sql="insert into brdfiles(serial,fileid,filename) values(brd_serial2.nextval,?,?)";
				ps=conn.prepareStatement(sql);
				ps.setInt(1,serial);
				ps.setString(2, sysfile);
				
				cnt=ps.executeUpdate();
				if(cnt>0){
					conn.commit();
					flag=true;
				}else{
					conn.rollback();
				}
				
				ps.close();
				conn.close();
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return flag;
	}

	
	public void computePage(){
		totPage = (int)Math.ceil(totSize*1.0 / listSize);//ceil은 반환타입이 long type이며 정수형으로 바꿔주고 1.0을 곱하기 때문에계산해야지됨!
		totBlock= (int)Math.ceil(totPage*1.0 / blockSize);
		
		nowBlock= (int)Math.ceil(nowPage*1.0/ blockSize);//현재페이지
		
		endPage = nowBlock * blockSize;//총 페이지가 7이고, 현재페이지 3 블럭 2 이면 12|34|56|7X X페이지는
		startPage= endPage-blockSize+1;
		if(totPage<endPage) endPage=totPage; //총 페이지 수 7을 마지막 페이지로 고정시켜주는거!
		
		endNo= nowPage*listSize;
		startNo= endNo-listSize+1;
		if(totSize<endNo)endNo=totSize;
		
	}
	
	
	// *****************************************************************
	//                list 화면에 뿌려줄 함수
	// *****************************************************************
	public Vector<BoardVo> list(){
		
		Vector<BoardVo> v = new Vector<>();
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		try {
			//전체 페이지수
			sql="select count(serial) cnt from board where (mid like ? or subject like ? or content like ? )and part=?";
			
			ps=conn.prepareStatement(sql);
			
			ps.setString(1, "%" + vo.getFind() + "%");
			ps.setString(2, "%" + vo.getFind() + "%");
			ps.setString(3, "%" + vo.getFind() + "%");
			ps.setString(4, vo.getPart());
			
			
			rs = ps.executeQuery();
			rs.next();
			totSize=rs.getInt("cnt");
			computePage();
			
		
			// part 에 따라서 게시글을 뿌려주기 위해서 mid, subject, content 는 논리적으로 묶어준다.
			sql =  "select * from (select rownum no,"
					+ " a.* from (select * from board where (mid like ? or subject like ? or content like ?)"
					+ " and part=? order by grp desc, deep asc)a"
					+ ") where no between ? and ?";
			
		
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, "%" + vo.getFind() + "%");
			ps.setString(2, "%" + vo.getFind() + "%");
			ps.setString(3, "%" + vo.getFind() + "%");
			ps.setString(4, vo.getPart());
			ps.setInt(5, startNo);
			ps.setInt(6, endNo);
			
			
			 rs = ps.executeQuery();
			
			while(rs.next()){
				BoardVo vo = new BoardVo();
				
				vo.setSerial(rs.getInt("serial"));
				vo.setMid(rs.getString("mid"));
				vo.setSubject(rs.getString("subject"));
				vo.setContent(rs.getString("content"));
				vo.setHit(rs.getInt("hit"));
				vo.setPart(rs.getString("part"));
				vo.setMdate(rs.getDate("mdate"));
				vo.setGrp(rs.getInt("grp"));

				v.add(vo);
			}
			
			rs.close();
			ps.close();
			conn.close();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return v;
	}
	public boolean repInsert(){

		boolean flag=false;
		try {
			MultipartRequest mr = 
					new MultipartRequest(vo.getRequest(), uploadPath,
							fileSize, fileEncoding, 
							new DefaultFileRenamePolicy());
			
			String mid=mr.getParameter("mid");
			String subject=mr.getParameter("subject");
			String content=mr.getParameter("content");
			String pwd=mr.getParameter("pwd");
			String part=mr.getParameter("part");
			String orifile=mr.getOriginalFileName("filename");
			String sysfile=mr.getFilesystemName("filename");
			
			int grp=Integer.parseInt(mr.getParameter("grp"));
			String deep=mr.getParameter("deep");
			
			//DB저장
			String sql="insert all into board(serial,part,mid,subject,content,pwd,grp,deep,mdate,hit) "
					+ "values(brd_serial1.nextval,?,?,?,?,?,?, ? || brd_serial1.currval,sysdate,100)";
			if(sysfile !=null){//파일있을때
				sql+=" into brdfiles(serial,fileid,filename) values(brd_serial2.nextval,brd_serial1.currval,?)";
				}
				sql+= " select*from dual";
			try {
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1,part );
				ps.setString(2,mid );
				ps.setString(3,subject );				
				ps.setString(4,content );
				ps.setString(5,pwd );
				ps.setInt(6, grp);
				ps.setString(7, deep + "-");
				
				if(sysfile != null){
					ps.setString(8, sysfile);					
				}
				
				
				int cnt=ps.executeUpdate();
				if(cnt>0) flag=true;
				
				
				
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			System.out.println("mid:"+mid);
			System.out.println("ori:"+orifile);
			System.out.println("sys:"+sysfile);
		} catch (IOException e) {			
			e.printStackTrace();
		}		
		
		
		return flag;
	}

	public BoardVo getVo() {
		return vo;
	}

	public void setVo(BoardVo vo) {
		this.vo = vo;
	}
	public void fileDel(String oldfile,int serial){
		sql="delete from brdfiles where fileid=? and filename=?";
	try{
		PreparedStatement ps= conn.prepareStatement(sql);
		
		ps.setInt(1, serial);
		ps.setString(2, oldfile);
		ps.executeUpdate();
		
		File file = new File(uploadPath+"/"+oldfile);
		if(file.exists())file.delete();
		
	}catch(Exception ex){
		ex.printStackTrace();
	}
	
	}
	public String getUploadPath() {
		return uploadPath;
	}

	public void setUploadPath(String uploadPath) {
		this.uploadPath = uploadPath;
	}

	public int getListSize() {
		return listSize;
	}

	public void setListSize(int listSize) {
		this.listSize = listSize;
	}

	public int getBlockSize() {
		return blockSize;
	}

	public void setBlockSize(int blockSize) {
		this.blockSize = blockSize;
	}

	public int getTotSize() {
		return totSize;
	}

	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}

	public int getTotPage() {
		return totPage;
	}

	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}

	public int getTotBlock() {
		return totBlock;
	}

	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}

	public int getNowPage() {
		return nowPage;
	}

	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}

	public int getNowBlock() {
		return nowBlock;
	}

	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}

	public int getStartNo() {
		return startNo;
	}

	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}

	public int getEndNo() {
		return endNo;
	}

	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}

	public int getStartPage() {
		return startPage;
	}

	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}

	public int getEndPage() {
		return endPage;
	}

	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}

}
